﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using UncleBobWebService.Models;
using UncleBobWebService.ServiceRequester;


namespace UncleBobWebService.Database
{
    class HMCommentDBAdapter : IDBAdapter
    {

        public override Message CreateData(Message msg, Object applicant)
        {                   
            msg.Result = true;

            CommentModel comment = (CommentModel)msg.Data;

            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = string.Format("INSERT INTO JobAppComments (AppID, UserID, Comment) VALUES ({0}, {1}, '{2}')", comment.AppID, comment.UserID, comment.Comment);
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    msg.Result = false;
                    return msg;
                }
                finally
                {
                    connection.Close();
                }
            }

            return msg;
        }


        public override Message SearchForSpecificData(Message msg, Object app)
        {
            CommentModel comment = msg.Data as CommentModel;
            mTable = new DataTable();
            mCommand = mConnection.CreateCommand();
            mCommand.CommandText = string.Format("SELECT * FROM JobAppComments WHERE AppID = {0} AND UserID = {1}", comment.AppID, comment.UserID);            
            SqlDataAdapter mAdapter = new SqlDataAdapter(mCommand);

            try
            {
                mConnection.Open();
                mAdapter.Fill(mTable);
                mConnection.Close();

                if (mTable.Rows.Count != 0)
                {
                    msg.Result = true;
                    comment.ID = int.Parse(mTable.Rows[0]["ID"].ToString());
                    comment.Comment = mTable.Rows[0]["Comment"].ToString();
                }
                else
                {
                    msg.Result = false;
                    msg.Data = new String(string.Format("No comments found.").ToCharArray());
                }
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;
        }


        public override Message SearchForAllData(Message msg)
        {
            CommentModel comment = msg.Data as CommentModel;
            mTable = new DataTable();
            mCommand = mConnection.CreateCommand();
            mCommand.CommandText = string.Format("SELECT * FROM JobAppComments WHERE AppID = {0}", comment.AppID);
            SqlDataAdapter mAdapter = new SqlDataAdapter(mCommand);

            try
            {
                mConnection.Open();
                mAdapter.Fill(mTable);
                mConnection.Close();

                if (mTable.Rows.Count != 0)
                {
                    msg.Result = true;
                    comment.ID = int.Parse(mTable.Rows[0]["ID"].ToString());
                    comment.Comment = mTable.Rows[0]["Comment"].ToString();
                }
                else
                {
                    comment.Comment = "";
                }
            }
            catch (Exception error)
            {
                msg.Result = false;
                msg.Data = new String(error.Message.ToCharArray());
            }

            return msg;
        }


        public override Message EditSpecificData(Message msg, Object data)
        {
            SqlDataAdapter SqlAdapter;
            DataTable dt = new DataTable();
            CommentModel commentContainer = msg.Data as CommentModel;
          
            using (SqlConnection connection = new SqlConnection(mConnectionString))
            using (SqlCommand command = connection.CreateCommand())
            {

                command.CommandText = string.Format("UPDATE JobAppComments SET Comment='{0}' WHERE AppID = {1}",
                                                     commentContainer.Comment, commentContainer.AppID);

                SqlAdapter = new SqlDataAdapter(command);

                try
                {
                    connection.Open();
                    SqlAdapter.Fill(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    connection.Close();
                    msg.Result = false;
                }
                finally
                {
                    connection.Close();
                }
            }

            return msg;
        }


        public override Message DeleteSpecificData(Message msg, Object data)
        {
            throw new NotImplementedException();
        }


        public override Message DeleteAllData(Message msg)
        {
            throw new NotImplementedException();
        }
    }
}
